package cmpe273.proj1.dao;

import static cmpe273.proj1.dao.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map.Entry;

import cmpe273.proj1.model.Course;
import cmpe273.proj1.model.Instructor;

/**
 * This class represents a concrete JDBC implementation of the {@link InstructorDAO} interface.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public class InstructorDAOJDBC implements InstructorDAO {

	// Constants ----------------------------------------------------------------------------------

	private static final String SQL_FIND_BY_ID =
			"SELECT i.Instructor_ID, i.InstructorId, i.Department, i.OfficeHours, i.OfficeLocation, i.FirstName, i.LastName, i.Address, i.City, i.State, i.ZipCode, c.Course_ID, c.Name, c.Section, c.MeetingTime, c.Location " +
					"FROM instructor i " +
					"LEFT JOIN taught_by t ON i.Instructor_ID = t.Instructor_ID " +
					"LEFT JOIN course c ON c.Course_ID = t.course_ID " +
					"WHERE i.Instructor_ID = ? " +
					"ORDER BY Instructor_ID " +
					"LIMIT 1";
	private static final String SQL_FIND_BY_SID =
			"SELECT i.Instructor_ID, i.InstructorId, i.Department, i.OfficeHours, i.OfficeLocation, i.FirstName, i.LastName, i.Address, i.City, i.State, i.ZipCode, c.Course_ID, c.Name, c.Section, c.MeetingTime, c.Location " +
					"FROM instructor i " +
					"LEFT JOIN taught_by t ON i.Instructor_ID = t.Instructor_ID " +
					"LEFT JOIN course c ON c.Course_ID = t.course_ID " +
					"WHERE i.InstructorId = ? " +
					"ORDER BY Instructor_ID " +
					"LIMIT 1";
	private static final String SQL_FIND_BY_COURSE =
			"SELECT i.Instructor_ID, i.InstructorId, i.Department, i.OfficeHours, i.OfficeLocation, i.FirstName, i.LastName, i.Address, i.City, i.State, i.ZipCode " +
					"FROM instructor i, course c, taught_by t " +
					"WHERE i.Instructor_ID = t.Instructor_ID and c.Course_ID = t.course_ID AND t.course_ID = ? " +
					"ORDER BY Instructor_ID " +
					"LIMIT 100";
	private static final String SQL_FIND_ALL =
			"SELECT Instructor_ID, InstructorId, Department, OfficeHours, OfficeLocation, FirstName, LastName, Address, City, State, ZipCode FROM instructor ORDER BY Instructor_ID";
	private static final String SQL_FIND_ALL_WITH_COURSES =
			"SELECT i.Instructor_ID, i.InstructorId, i.Department, i.OfficeHours, i.OfficeLocation, i.FirstName, i.LastName, i.Address, i.City, i.State, i.ZipCode, c.Course_ID, c.Name, c.Section, c.MeetingTime, c.Location " +
					"FROM instructor i " + 
					"LEFT JOIN taught_by t ON i.Instructor_ID = t.Instructor_ID " +
					"LEFT JOIN course c ON c.Course_ID = t.course_ID " +
					"ORDER BY Instructor_ID";
	private static final String SQL_FIND_BY_GENERIC =
			"SELECT i.Instructor_ID, i.InstructorId, i.Department, i.OfficeHours, i.OfficeLocation, i.FirstName, i.LastName, i.Address, i.City, i.State, i.ZipCode FROM instructor i";
	private static final String SQL_FIND_BY_GENERIC_WITH_COURSES =
			"SELECT i.Instructor_ID, i.InstructorId, i.Department, i.OfficeHours, i.OfficeLocation, i.FirstName, i.LastName, i.Address, i.City, i.State, i.ZipCode, c.Course_ID, c.Name, c.Section, c.MeetingTime, c.Location " +
					"FROM instructor i " +
					"LEFT JOIN taught_by t ON i.Instructor_ID = t.Instructor_ID " +
					"LEFT JOIN course c ON c.Course_ID = t.course_ID";
	private static final String SQL_INSERT =
			"INSERT INTO instructor (InstructorId, Department, OfficeHours, OfficeLocation, FirstName, LastName, Address, City, State, ZipCode) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String SQL_UPDATE =
			"UPDATE instructor SET  InstructorId = ?, Department = ?, OfficeHours = ?, OfficeLocation = ?, FirstName = ?, LastName = ?, Address = ?, City = ?, State= ?, ZipCode = ? WHERE Instructor_ID = ?";
	private static final String SQL_DELETE =
			"DELETE FROM instructor WHERE Instructor_ID = ?";
	private static final String SQL_INSTRUCTOR_ADDTEACHER =
			"INSERT INTO taught_by (Instructor_ID, Course_ID) VALUES (?, ?)";
	private static final String SQL_INSTRUCTOR_REMOVETEACHER =
			"DELETE from taught_by WHERE Instructor_ID = ? AND Course_ID = ?";

	// Vars ---------------------------------------------------------------------------------------

	private DAOFactory daoFactory;

	// Constructors -------------------------------------------------------------------------------

	/**
	 * Construct an User DAO for the given DAOFactory. Package private so that it can be constructed
	 * inside the DAO package only.
	 * @param daoFactory The DAOFactory to construct this User DAO for.
	 */
	InstructorDAOJDBC(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	// Actions ------------------------------------------------------------------------------------
	
	@Override
	public Instructor find(Long id) throws DAOException {
		return find(SQL_FIND_BY_ID, id);
	}

	@Override
	public Instructor find(String id) throws DAOException {
		return find(SQL_FIND_BY_SID, id);
	}

	/**
	 * Returns the user from the database matching the given SQL query with the given values.
	 * @param sql The SQL query to be executed in the database.
	 * @param values The PreparedStatement values to be set.
	 * @return The user from the database matching the given SQL query with the given values.
	 * @throws DAOException If something fails at database level.
	 */
	private Instructor find(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Instructor instructor = null;
		Instructor newInstructor = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();

			//loop for courses
			while (resultSet.next()) {
				newInstructor = map(instructor, resultSet);

				//when new instructor created, place as current instructor
				if(newInstructor != null)
					instructor = newInstructor;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return instructor;
	}

	@Override
	public List<Instructor> list(int count, int page, boolean getCourses) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Instructor> instructors = new ArrayList<Instructor>();
		Instructor instructor = null;
		Instructor newInstructor = null;
		
		int maxReturn = (count > DAOUtil.MAX_ROWS) ? DAOUtil.MAX_ROWS : count;
		int offset = (page > 0) ? page - 1 : 0;
		String limitQuery =  String.format(" LIMIT %d OFFSET %d", maxReturn, maxReturn * offset);

		try {
			connection = daoFactory.getConnection();

			if(getCourses) {
				preparedStatement = prepareStatement(connection, SQL_FIND_ALL_WITH_COURSES + limitQuery, false);
				resultSet = preparedStatement.executeQuery();
				//loop for instructors and courses
				while (resultSet.next()) {
					newInstructor = map(instructor, resultSet);

					//when new instructor created, place as current instructor and add to list
					if(newInstructor != null) {
						instructor = newInstructor;
						instructors.add(instructor);
					}
				}
			} else {
				preparedStatement = prepareStatement(connection, SQL_FIND_ALL + limitQuery, false);
				resultSet = preparedStatement.executeQuery();
				while (resultSet.next()) {
					instructors.add(map(null, resultSet));
				}
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return instructors;
	}

	@Override
	public List<Instructor> search(Instructor searchParams, boolean getCourses) throws DAOException {
		//prepare
		StringBuilder query = new StringBuilder(getCourses ? SQL_FIND_BY_GENERIC_WITH_COURSES : SQL_FIND_BY_GENERIC);
		HashMap<String, Object> queryValues = new HashMap<String, Object>();

		//build query
		if(searchParams.getId() != null && searchParams.getId() > 0)
			queryValues.put(" Instructor_ID = ?", searchParams.getId());
		if(!searchParams.getInstructorId().isEmpty())
			queryValues.put(" InstructorId = ?", searchParams.getInstructorId());
		if(!searchParams.getDepartment().isEmpty())
			queryValues.put(" Department LIKE ?", wrapLikeCompare(searchParams.getDepartment(), true));
		if(!searchParams.getOfficeHours().isEmpty())
			queryValues.put(" OfficeHours LIKE ?", wrapLikeCompare(searchParams.getOfficeHours(), true));
		if(!searchParams.getOfficeLocation().isEmpty())
			queryValues.put(" OfficeLocation LIKE ?", wrapLikeCompare(searchParams.getOfficeLocation(), true));
		if(!searchParams.getFirstName().isEmpty())
			queryValues.put(" FirstName LIKE ?", wrapLikeCompare(searchParams.getFirstName(), false));
		if(!searchParams.getLastName().isEmpty())
			queryValues.put(" LastName LIKE ?", wrapLikeCompare(searchParams.getLastName(), false));
		if(!searchParams.getAddress().isEmpty())
			queryValues.put(" Address LIKE ?", wrapLikeCompare(searchParams.getAddress(), true));
		if(!searchParams.getCity().isEmpty())
			queryValues.put(" City LIKE ?", wrapLikeCompare(searchParams.getCity(), false));
		if(!searchParams.getState().isEmpty())
			queryValues.put(" State = ?", searchParams.getState());
		if(!searchParams.getZipCode().isEmpty())
			queryValues.put(" ZipCode LIKE ?", wrapLikeCompare(searchParams.getZipCode(), false));

		if(queryValues.size() == 0)
			return list(DAOUtil.MAX_ROWS, 1, getCourses);

		//transmute
		Object[] values = new Object[queryValues.size()];
		query.append(" WHERE");

		int idx = 0;
		for(Entry<String, Object> entry : queryValues.entrySet()) {
			if(idx > 0)
				query.append(" AND");
			query.append(entry.getKey());
			values[idx++] = entry.getValue();
		}
		query.append(String.format(" ORDER BY Instructor_ID LIMIT %d", DAOUtil.MAX_ROWS));

		//Copy-paste from find(...) and list()
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Instructor> instructors = new ArrayList<Instructor>();
		Instructor instructor = null;
		Instructor newInstructor = null;

		try {
			connection = daoFactory.getConnection();

			preparedStatement = prepareStatement(connection, query.toString(), false, values);
			resultSet = preparedStatement.executeQuery();
			//loop for instructors and courses
			while (resultSet.next()) {
				newInstructor = map(instructor, resultSet);

				//when new instructor created, place as current instructor and add to list
				if(newInstructor != null) {
					instructor = newInstructor;
					instructors.add(instructor);
				}
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return instructors;
		//end Copy-past from find(...) and list()
	}

	@Override
	public List<Instructor> searchInstructorsTeaching(Course course) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Instructor> instructors = new ArrayList<Instructor>();

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_FIND_BY_COURSE, false, course.getId());
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				instructors.add(map(null, resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return instructors;
	}

	@Override
	public void create(Instructor instructor) throws IllegalArgumentException, DAOException {
		if (instructor.getId() != null) {
			throw new IllegalArgumentException("Instructor is already created, the Instructor ID is not null.");
		}

		Object[] values = {
				instructor.getInstructorId(),
				instructor.getDepartment(),
				instructor.getOfficeHours(),
				instructor.getOfficeLocation(),
				instructor.getFirstName(),
				instructor.getLastName(),
				instructor.getAddress(),
				instructor.getCity(),
				instructor.getState(),
				instructor.getZipCode()
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_INSERT, true, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Creating instructor failed, no rows affected.");
			}
			generatedKeys = preparedStatement.getGeneratedKeys();
			if (generatedKeys.next()) {
				instructor.setId(generatedKeys.getLong(1));
			} else {
				throw new DAOException("Creating instructor failed, no generated key obtained.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, generatedKeys);
		}
	}

	@Override
	public void update(Instructor instructor) throws DAOException {
		if (instructor.getId() == null) {
			throw new IllegalArgumentException("User is not created yet, the user ID is null.");
		}
		
		Object[] values = {
				instructor.getInstructorId(),
				instructor.getDepartment(),
				instructor.getOfficeHours(),
				instructor.getOfficeLocation(),
				instructor.getFirstName(),
				instructor.getLastName(),
				instructor.getAddress(),
				instructor.getCity(),
				instructor.getState(),
				instructor.getZipCode(),
				instructor.getId()
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_UPDATE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Updating user failed, no rows affected.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
	}

	@Override
	public void addCourse(Instructor i, Course c) throws DAOException {
		Object[] values = { 
				i.getId(),
				c.getId()
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_INSTRUCTOR_ADDTEACHER, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Adding instructor failed, no rows affected.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
	}

	@Override
	public void removeCourse(Instructor i, Course c) throws IllegalArgumentException, DAOException {
		Object[] values = { 
				i.getId(),
				c.getId()
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_INSTRUCTOR_REMOVETEACHER, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Removing instructor failed, no rows affected.");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
	}

	@Override
	public void delete(Instructor instructor) throws DAOException {
		Object[] values = { 
				instructor.getId()
		};

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_DELETE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("Deleting instructor failed, no rows affected.");
			} else {
				instructor.setId(null);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
	}

	// Helpers ------------------------------------------------------------------------------------

	/**
	 * Map the current row of the given ResultSet to a Instructor, optionally with course.
	 * @param currentInstructor 
	 * @param resultSet The ResultSet of which the current row is to be mapped to a Instructor.
	 * @return The mapped Instructor from the current row of the given ResultSet.
	 * @throws SQLException If something fails at database level.
	 */
	private static Instructor map(Instructor currentInstructor, ResultSet resultSet) throws SQLException {
		Long id = resultSet.getLong("Instructor_ID");
		boolean isNewInstructor = false;

		//if new set basic instructor properties
		if(currentInstructor == null || currentInstructor.getId() != id) {
			currentInstructor = new Instructor();
			currentInstructor.setId(id);
			currentInstructor.setInstructorId(resultSet.getString("InstructorId"));
			currentInstructor.setDepartment(resultSet.getString("Department"));
			currentInstructor.setOfficeHours(resultSet.getString("OfficeHours"));
			currentInstructor.setOfficeLocation(resultSet.getString("OfficeLocation"));
			currentInstructor.setFirstName(resultSet.getString("FirstName"));
			currentInstructor.setLastName(resultSet.getString("LastName"));
			currentInstructor.setAddress(resultSet.getString("Address"));
			currentInstructor.setCity(resultSet.getString("City"));
			currentInstructor.setState(resultSet.getString("State"));
			currentInstructor.setZipCode(resultSet.getString("ZipCode"));
			
			isNewInstructor = true;
		}

		//add course information (if exists)
		Long courseId = 0L;
		try {
			courseId = resultSet.getLong("Course_ID");
		} catch(SQLException e) { } //ignore "column doesn't exist" errors
		if(courseId > 0) {
			Course c = new Course();
			c.setId(courseId);
			c.setCourseName(resultSet.getString("Name"));
			c.setSection(resultSet.getString("Section"));
			c.setTimes(resultSet.getString("MeetingTime"));
			c.setLocation(resultSet.getString("Location"));

			currentInstructor.getTaughtCourses().add(c);
		}

		if(isNewInstructor)
			return currentInstructor;
		return null;
	}
}